package com.siemens.industrial.web.mvc.dao.partner;

import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.List;
import java.util.Map;

/**
 * Created by shuan on 2016/11/15.
 */
@Service
public class PartnerChartsDao {
    @Resource
    JdbcTemplate jdbcTemplate;

    private static final String databaseTag = "{database}";
    private static final String getSameDeviceDiffTypeTime = "SELECT \n" +
            "    f.date,\n" +
            "    f.device_id,\n" +
            "    f.deviceName,\n" +
            "    f.idle_reason,\n" +
            "    f.diftime diftime,\n" +
            "    f.meta_definition\n" +
            "FROM\n" +
            "    (SELECT \n" +
            "        dsa.date,\n" +
            "            asd.device_id,\n" +
            "            asd.deviceName,\n" +
            "            asd.idle_reason,\n" +
            "            asd.diftime,\n" +
            "            asd.meta_definition\n" +
            "    FROM\n" +
            "        (SELECT \n" +
            "        (ADDDATE(date(?), numlist.id)) AS `date`\n" +
            "    FROM\n" +
            "        (SELECT \n" +
            "        n1.i + n10.i * 10 + n100.i * 100 AS id\n" +
            "    FROM\n" +
            "        `"+databaseTag+"`.num n1\n" +
            "    CROSS JOIN `"+databaseTag+"`.num AS n10\n" +
            "    CROSS JOIN `"+databaseTag+"`.num AS n100) AS numlist\n" +
            "    WHERE\n" +
            "        ADDDATE(date(?), numlist.id) <= date(?)) dsa\n" +
            "    LEFT JOIN (SELECT \n" +
            "        c.device_id,c.deviceName, SUM(c.time) difTime, c.idle_reason,c.meta_definition, c.date\n" +
            "    FROM\n" +
            "        (SELECT \n" +
            "        a.device_id,\n" +
            "        d.device_unique_id as deviceName,\n" +
            "        a.idle_reason,\n" +
            "            b.meta_definition,\n" +
            "            (UNIX_TIMESTAMP(end_time) - UNIX_TIMESTAMP(start_time)) time,\n" +
            "            DATE_FORMAT(a.start_time, '%Y-%m-%d') 'date'\n" +
            "    FROM\n" +
            "        `"+databaseTag+"`.device_idle_info a\n" +
            "    RIGHT JOIN `"+databaseTag+"`.metadata b ON a.idle_reason = b.meta_id\n" +
            "    left join `"+databaseTag+"`.device d on d.device_id = a.device_id\n" +
            "    WHERE\n" +
            "        a.device_id = ?\n" +
            "            AND b.meta_key = 'IDLE'\n" +
            "            AND (DATE(a.start_time) BETWEEN date(?) AND date(?))) c\n" +
            "    GROUP BY c.date,c.idle_reason,c.device_id,c.deviceName,c.meta_definition) asd ON asd.date = dsa.date) f\n" +
            "ORDER BY f.date";
    private static final String getDeviceIdleReason = "SELECT DISTINCT\n" +
            "    (b.meta_definition) reason\n" +
            "FROM\n" +
            "    `"+databaseTag+"`.device_idle_info a\n" +
            "    left join `"+databaseTag+"`.metadata b on a.idle_reason = b.meta_id\n" +
            "WHERE\n" +
            "    a.device_id = ?\n" +
            "        and b.meta_key = 'IDLE' \n" +
            "        AND (date(a.start_time)  between date(?) and date(?))";

    private static final String getSameDeviceSameTypeTime = "SELECT f.date,f.device_id,f.diftime diftime,f.meta_definition\n" +
            "            FROM(SELECT \n" +
            "            dsa.date, case when asd.device_id is null then ? else asd.device_id end as device_id,\n" +
            "            ifnull(asd.diftime,0) as diftime, asd.meta_definition\n" +
            "            FROM\n" +
            "            (SELECT\n" +
            "                (ADDDATE(date(?), numlist.id)) AS `date`\n" +
            "            FROM\n" +
            "                (SELECT \n" +
            "                n1.i + n10.i * 10 + n100.i * 100 AS id\n" +
            "            FROM\n" +
            "                `"+databaseTag+"`.num n1\n" +
            "            CROSS JOIN `"+databaseTag+"`.num AS n10\n" +
            "            CROSS JOIN `"+databaseTag+"`.num AS n100) AS numlist\n" +
            "            WHERE\n" +
            "                ADDDATE(date(?), numlist.id) <= date(?)) dsa\n" +
            "                LEFT JOIN\n" +
            "            (SELECT \n" +
            "                c.device_id, SUM(c.time) difTime, c.meta_definition, c.date\n" +
            "            FROM\n" +
            "                (SELECT \n" +
            "                a.device_id,\n" +
            "                    a.start_time,\n" +
            "                    b.meta_definition,\n" +
            "                    (UNIX_TIMESTAMP(end_time) - UNIX_TIMESTAMP(start_time)) time,\n" +
            "                    DATE_FORMAT(a.start_time, '%Y-%m-%d') 'date'\n" +
            "            FROM\n" +
            "                `"+databaseTag+"`.device_idle_info a\n" +
            "            RIGHT JOIN `"+databaseTag+"`.metadata b ON a.idle_reason = b.meta_id\n" +
            "            WHERE\n" +
            "                a.device_id = ?\n" +
            "                    AND b.meta_key = 'IDLE'\n" +
            "                    AND (date(a.start_time)  between date(?) and date(?))\n" +
            "                    AND a.idle_reason = ?) c\n" +
            "            GROUP BY c.date ,c.device_id, c.meta_definition) asd ON asd.date = dsa.date) f  order by f.date";

    private static final String getSameReasonDeviceTypeTime = "SELECT \n" +
            "    f.date,\n" +
            "    f.device_id,\n" +
            "    f.device_name,\n" +
            "    f.idle_reason,\n" +
            "    f.diftime diftime,\n" +
            "    f.meta_definition\n" +
            "FROM\n" +
            "    (SELECT \n" +
            "        dsa.date,\n" +
            "            asd.device_id,\n" +
            "            asd.device_name,\n" +
            "            asd.idle_reason,\n" +
            "            asd.diftime,\n" +
            "            asd.meta_definition\n" +
            "    FROM\n" +
            "        (SELECT \n" +
            "        (ADDDATE(date(?), numlist.id)) AS `date`\n" +
            "    FROM\n" +
            "        (SELECT \n" +
            "        n1.i + n10.i * 10 + n100.i * 100 AS id\n" +
            "    FROM\n" +
            "        `"+databaseTag+"`.num n1\n" +
            "    CROSS JOIN `"+databaseTag+"`.num AS n10\n" +
            "    CROSS JOIN `"+databaseTag+"`.num AS n100) AS numlist\n" +
            "    WHERE\n" +
            "        ADDDATE(date(?), numlist.id) <= date(?)) dsa\n" +
            "    LEFT JOIN (SELECT \n" +
            "        c.device_id,\n" +
            "            c.device_name,\n" +
            "            c.idle_reason,\n" +
            "            SUM(c.time) difTime,\n" +
            "            c.meta_definition,\n" +
            "            c.date\n" +
            "    FROM\n" +
            "        (SELECT \n" +
            "        a.device_id,\n" +
            "            a.idle_reason,\n" +
            "            d.device_unique_id AS device_name,\n" +
            "            a.start_time,\n" +
            "            b.meta_definition,\n" +
            "            (UNIX_TIMESTAMP(end_time) - UNIX_TIMESTAMP(start_time)) time,\n" +
            "            DATE_FORMAT(a.start_time, '%Y-%m-%d') 'date'\n" +
            "    FROM\n" +
            "        `"+databaseTag+"`.device_idle_info a\n" +
            "    RIGHT JOIN `"+databaseTag+"`.metadata b ON a.idle_reason = b.meta_id\n" +
            "    LEFT JOIN `"+databaseTag+"`.device d ON a.device_id = d.device_id\n" +
            "    WHERE\n" +
            "        a.idle_reason = ?\n" +
            "            AND b.meta_key = 'IDLE'\n" +
            "            AND (DATE(a.start_time) BETWEEN date(?) AND date(?))) c\n" +
            "    GROUP BY c.date , c.device_id , c.meta_definition) asd ON asd.date = dsa.date) f\n" +
            "ORDER BY f.date";

    private static final String getSameReasonDevice = "SELECT \n" +
            "    distinct(a.device_id),(b.device_unique_id)device_name\n" +
            "FROM\n" +
            "    `"+databaseTag+"`.device_idle_info a\n" +
            "        INNER JOIN\n" +
            "    `"+databaseTag+"`.device b ON a.device_id = b.device_id\n" +
            "    right join `"+databaseTag+"`.metadata c\n" +
            "    on a.idle_reason = c.meta_id\n" +
            "    where c.meta_id = ?" +
            "    and c.meta_key = 'IDLE' \n" +
            "   and (date(a.start_time) between date(?) and date(?))";

//    private static final String getShiftState = "SELECT \n" +
//            "    a.plan_state\n" +
//            "FROM\n" +
//            "    `"+databaseTag+"`.device_shift_relation a\n" +
//            "WHERE\n" +
//            "    a.begin_date <= DATE_FORMAT(?, '%Y-%m-%d')\n" +
//            "        AND a.end_date >= DATE_FORMAT(?, '%Y-%m-%d')\n" +
//            "        AND a.start_time = ?\n" +
//            "        AND a.end_time = ?\n" +
//            "        AND device_id = ?;";

    private static final String getDeviceFreeReason = "SELECT \n" +
            "   f.device_id,\n" +
            "   f.deviceName,\n" +
            "   SUM(f.time) time,\n" +
            "   f.meta_key,\n" +
            "   f.meta_definition\n" +
            "   FROM\n" +
            "   (SELECT \n" +
            "       c.device_id,\n" +
            "           d.device_unique_id AS deviceName,\n" +
            "           (UNIX_TIMESTAMP(c.end_time) - UNIX_TIMESTAMP(c.start_time)) time,\n" +
            "           b.meta_key,\n" +
            "           b.meta_definition\n" +
            "   FROM\n" +
            "       `"+databaseTag+"`.metadata b\n" +
            "   RIGHT JOIN (SELECT \n" +
            "       a.device_id,\n" +
            "           CASE\n" +
            "               WHEN a.start_time < DATE_ADD(?, INTERVAL ? HOUR_SECOND) THEN DATE_ADD(?, INTERVAL ? HOUR_SECOND)\n" +
            "               ELSE a.start_time\n" +
            "           END AS start_time,\n" +
            "           CASE\n" +
            "               WHEN a.end_time > DATE_ADD(?, INTERVAL ? HOUR_SECOND) THEN DATE_ADD(?, INTERVAL ? HOUR_SECOND)\n" +
            "               ELSE a.end_time\n" +
            "           END AS end_time,\n" +
            "           a.idle_reason\n" +
            "   FROM\n" +
            "       `"+databaseTag+"`.device_idle_info a\n" +
            "   WHERE\n" +
            "       a.device_id = ?\n" +
            "           AND (" +
            "               (a.start_time <= DATE_ADD(?, INTERVAL ? HOUR_SECOND) AND a.end_time > DATE_ADD(?, INTERVAL ? HOUR_SECOND))\n" +
            "           OR (a.start_time > DATE_ADD(?, INTERVAL ? HOUR_SECOND) AND a.start_time < DATE_ADD(?, INTERVAL ? HOUR_SECOND)))\n" +
            "   ) c ON b.meta_id = c.idle_reason\n" +
            "   LEFT JOIN `"+databaseTag+"`.device d ON c.device_id = d.device_id) f where f.meta_key='IDLE'\n" +
            "   GROUP BY f.meta_definition,f.device_id,f.deviceName,f.meta_key;";

    private static final String getIdleReasons = "select a.meta_id,a.meta_definition from `"+databaseTag+"`.metadata a where a.meta_key = 'IDLE';";

    private static final String getFaultType = "select a.meta_id,a.meta_definition from `"+databaseTag+"`.metadata a where a.meta_key = 'FaultType';";

    private static final String findDeviceType = "SELECT distinct(a.device_type) as deviceType FROM `"+databaseTag+"`.device a;";

    private static final String findDeviceModelByType = "SELECT distinct(a.device_model) as deviceModel FROM `"+databaseTag+"`.device a where a.device_type = ?;";

    private static final String otherPeriod="SELECT (UNIX_TIMESTAMP(end_time) - UNIX_TIMESTAMP(start_time)) period,state,status_type FROM `"+databaseTag+"`.position_period where date(start_time)= date(?) and status_type =? and device_id=? order by start_time;";
    private static final String stateOfPeriod="SELECT * FROM `"+databaseTag+"`.state_info where (date(?)>=start_time and (date(?) <= end_time or end_time is null));";
    private static final String findDevicesByModelAndType = "SELECT a.device_id as deviceId,a.device_unique_id as deviceName FROM `"+databaseTag+"`.device a where a.device_type = ? and device_model = ? order by a.device_unique_id;";

    private static final String getDownTimeRepairTable = "select * from (SELECT * FROM `"+databaseTag+"`.device_idle_info where date(start_time)=date(?) and repair_info is not null and device_id = ? order by start_time) a inner join `"+databaseTag+"`.metadata b on a.idle_reason=b.meta_id;";

    private static final String findIdleReasonByDevice = "SELECT \n" +
            "    *\n" +
            "FROM\n" +
            "    `"+databaseTag+"`.metadata b\n" +
            "WHERE\n" +
            "    b.meta_key = 'IDLE'\n" +
            "        AND meta_id IN (SELECT DISTINCT\n" +
            "            (a.idle_reason)\n" +
            "        FROM\n" +
            "            `"+databaseTag+"`.device_idle_info a\n" +
            "        WHERE\n" +
            "            a.device_id = ?)";


    //获取：最近的系统性能参数
    private static final String findRecentlyIndexParam = "select device_id, date_id, \n" +
            "      case when meta_definition = 1 then work_time_diff / (86400 - stop_time_diff)\n" +
            "           when meta_definition = 0 then work_time_diff / 86400\n" +
            "      end availability,\n" +
            "       1 performance,\n" +
            "       1 quality,\n" +
            "      case when meta_definition = 1 then work_time_diff / (86400 - stop_time_diff)\n" +
            "           when meta_definition = 0 then work_time_diff / 86400\n" +
            "      end oee       \n" +
            "from ( SELECT device_id, date(start_time) date_id, m.meta_definition,\n" +
            "             sum(case when state = 1 then timestampdiff(second, start_time, end_time)\n" +
            "                   else 0 end ) work_time_diff,\n" +
            "             sum(case when state = -1 then timestampdiff(second, start_time, end_time)\n" +
            "                   else 0 end ) stop_time_diff\n" +
            "       FROM `"+databaseTag+"`.device_state_period d\n" +
            "       cross join (select * from `"+databaseTag+"`.metadata where meta_id = '1') m\n" +
            "       where state in (1, -1)\n" +
            "       and device_id = ?\n" +
            "       and date(start_time) = date(?)\n" +
            "       group by device_id, date(start_time), m.meta_definition\n" +
            "    ) a";

    private static final String findWorkStatus = "SELECT \n" +
            "    a.device_id,\n" +
            "    CASE\n" +
            "        WHEN a.state = -1 THEN '计划停机'\n" +
            "        WHEN a.state = 0 THEN 'idle'\n" +
            "        WHEN a.state = 1 THEN '正常'\n" +
            "    END AS state,\n" +
            "    sum((UNIX_TIMESTAMP(end_time) - UNIX_TIMESTAMP(start_time))) AS time\n" +
            "FROM\n" +
            "    `"+databaseTag+"`.device_state_period a\n" +
            "WHERE\n" +
            "    DATE(a.start_time) = DATE(?)\n" +
            "        AND a.device_id = ?\n" +
            "GROUP BY a.state;";

    private static final String findDayIdleReason = "SELECT \n" +
            "    b.meta_id,\n" +
            "    case when total.totalTime = 0 then 0 else SUM(UNIX_TIMESTAMP(a.end_time) - UNIX_TIMESTAMP(a.start_time))/total.totalTime*100 end AS time,\n" +
            "    b.meta_definition\n" +
            "FROM\n" +
            "    `"+databaseTag+"`.device_idle_info a\n" +
            "        LEFT JOIN\n" +
            "    `"+databaseTag+"`.metadata b ON a.idle_reason = b.meta_id\n" +
            "       join (SELECT \n" +
            "                   ifnull(sum((UNIX_TIMESTAMP(a.end_time) - UNIX_TIMESTAMP(a.start_time))),0) AS totalTime\n" +
            "               FROM\n" +
            "                   `"+databaseTag+"`.device_idle_info a\n" +
            "                       LEFT JOIN\n" +
            "                   `"+databaseTag+"`.metadata b ON a.idle_reason = b.meta_id\n" +
            "               WHERE\n" +
            "                   a.device_id = ?\n" +
            "                       AND DATE(a.start_time) = DATE(?)\n" +
            "                       AND b.meta_key = 'IDLE') total\n" +
            "WHERE\n" +
            "    a.device_id = ?\n" +
            "        AND DATE(a.start_time) = DATE(?)\n" +
            "        AND b.meta_key = 'IDLE'\n" +
            "GROUP BY b.meta_id,total.totalTime,b.meta_definition;";


    private static final String getFaultTimePeriod = "SELECT * FROM `"+databaseTag+"`.device_idle_info where date(start_time)=date(?) and repair_info is not null and device_id = ? order by start_time;";

    private static final String getMTTRAndMTBF = "SELECT * FROM `"+databaseTag+"`.device_idle_info where repair_info is not null  and device_id =? and idle_reason=? and (date(start_time)<=date(?) and date(start_time) >=date(?)) ORDER BY start_time;";

    private static final String getAllData = "SELECT \n" +
            "    date_id, GROUP_CONCAT(repair_info) repair\n" +
            "FROM\n" +
            "    (SELECT \n" +
            "        date_id\n" +
            "    FROM\n" +
            "        `"+databaseTag+"`.v_num\n" +
            "    WHERE\n" +
            "        date_id BETWEEN DATE(?) AND DATE(?)) a\n" +
            "        LEFT JOIN\n" +
            "    (SELECT \n" +
            "        start_time, repair_info\n" +
            "    FROM\n" +
            "        `"+databaseTag+"`.device_idle_info\n" +
            "    WHERE\n" +
            "        repair_info IS NOT NULL\n" +
            "            AND device_id = ?\n" +
            "            AND idle_reason = ?\n" +
            "            AND (DATE(start_time) <= DATE(?)\n" +
            "            AND DATE(start_time) >= DATE(?))) b ON a.date_id = DATE(b.start_time)\n" +
            "GROUP BY date_id\n" +
            "ORDER BY date_id;";

    private static final String getWeekDownTimePieChartData = "select a.device_id,a.timePeriod,b.meta_id,b.meta_definition from (SELECT \n" +
            "    device_id,\n" +
            "    sum((UNIX_TIMESTAMP(end_time) - UNIX_TIMESTAMP(start_time))) timePeriod,\n" +
            "    idle_reason\n" +
            "FROM\n" +
            "    `"+databaseTag+"`.device_idle_info\n" +
            "WHERE\n" +
            "    device_id = ?\n" +
            "        AND date(start_time) BETWEEN date(?) AND date(?) group by idle_reason) a inner join `"+databaseTag+"`.metadata b on a.idle_reason=b.meta_id;";

    private static final String getWeekDownTimeBarChartData = "SELECT \n" +
            "    *\n" +
            "FROM\n" +
            "    `"+databaseTag+"`.v_num d\n" +
            "        LEFT JOIN\n" +
            "    (SELECT \n" +
            "        a.device_id,\n" +
            "            a.timePeriod,\n" +
            "            a.startTime,\n" +
            "            a.idle_reason,\n" +
            "            b.meta_definition\n" +
            "    FROM\n" +
            "        (SELECT \n" +
            "        device_id,\n" +
            "            SUM((UNIX_TIMESTAMP(end_time) - UNIX_TIMESTAMP(start_time))) timePeriod,\n" +
            "            DATE(start_time) startTime,\n" +
            "            idle_reason\n" +
            "    FROM\n" +
            "        `"+databaseTag+"`.device_idle_info\n" +
            "    WHERE\n" +
            "        device_id = ?\n" +
            "    GROUP BY DATE(start_time) , idle_reason) a\n" +
            "    INNER JOIN `"+databaseTag+"`.metadata b ON a.idle_reason = b.meta_id) c ON d.date_id = c.startTime\n" +
            "WHERE\n" +
            "    d.date_id BETWEEN DATE(?) AND DATE(?) order by d.date_id;";

    public List getSameDeviceDiffTypeTime(String database, String device, String startTime, String endTime){
        return jdbcTemplate.queryForList(getSameDeviceDiffTypeTime.replace(databaseTag,database),startTime,startTime,endTime,device,startTime,endTime);
    }

    //某一机床 某一时间段 发生闲置的 所有原因
    public List<Map<String,Object>> getDeviceIdleReason(String database, String device, String startTime, String endTime){
        return jdbcTemplate.queryForList(getDeviceIdleReason.replace(databaseTag,database),device,startTime,endTime);
    }

    //某一机床 某一时间段 发生闲置的 某一原因
    public List getSameDeviceSameTypeTime(String database,String device,String reason,String startTime,String endTime){
        return jdbcTemplate.queryForList(getSameDeviceSameTypeTime.replace(databaseTag,database),device,startTime,startTime,endTime,device,startTime,endTime,reason);
    }

    //某一原因 所有机床 发生闲置的 趋势
    public List getSameReasonDeviceTypeTime(String database,String reason,String startTime,String endTime){
        return jdbcTemplate.queryForList(getSameReasonDeviceTypeTime.replace(databaseTag,database),startTime,startTime,endTime,reason,startTime,endTime);
    }

    //某一原因 有一时段 发生闲置的 所有机床
    public List getSameReasonDevice(String database,String reason,String startTime,String endTime){
        return jdbcTemplate.queryForList(getSameReasonDevice.replace(databaseTag,database),reason,startTime,endTime);
    }

//    //某机床 某班次 状态
//    public List getShiftState(String database,String day,String startTime,String endTime,String device){
//        return jdbcTemplate.queryForList(getShiftState.replace(databaseTag,database),day,day,startTime,endTime,device);
//    }

    //某机床 某班次 发生闲置状态 原因 总体分布
    public List getDeviceFreeReason(String database,String start_time,String startDay,String day,String end_time,String device){
        return jdbcTemplate.queryForList(getDeviceFreeReason.replace(databaseTag,database),startDay,start_time,startDay,start_time,day,end_time,day,end_time,device,day,end_time,startDay,start_time,startDay,start_time,day,end_time);
    }

    //获取全部idle原因
    public List getIdleReasons(String database){
        return jdbcTemplate.queryForList(getIdleReasons.replace(databaseTag,database));
    }

    //获取全部FaultType
    public List getFaultType(String database) {
        return jdbcTemplate.queryForList(getFaultType.replace(databaseTag, database));
    }

    //按小时显示OEE
    public List<Map<String,Object>> findShiftServiceByNew(String database,String[] deviceIdArr,String day){
        String findServiceByShiftNewSql = "select l.device_id, l.date_id, l.shift_start_time, l.shift_end_time, d.device_unique_id as deviceName,\n" +
                "      case when meta_definition = 1 then work_time_diff / (shift_time_diff - stop_time_diff)\n" +
                "           when meta_definition = 0 then work_time_diff / shift_time_diff\n" +
                "      end availability,\n" +
                "       1 performance,\n" +
                "       1 quality,\n" +
                "      case when meta_definition = 1 then work_time_diff / (shift_time_diff - stop_time_diff)\n" +
                "           when meta_definition = 0 then work_time_diff / shift_time_diff\n" +
                "      end oee       \n" +
                "from (\n" +
                "   select device_id, date_id, shift_start_time, shift_end_time, \n" +
                "           timestampdiff(second, shift_start_time, shift_end_time) shift_time_diff,\n" +
                "           sum(case when state = 1 then \n" +
                "               timestampdiff(second, \n" +
                "                           if(start_time < shift_start_time, shift_start_time, start_time),\n" +
                "                           if(end_time > shift_end_time, shift_end_time, end_time))\n" +
                "               else 0 end) work_time_diff,\n" +
                "           sum(case when state = -1 then \n" +
                "               timestampdiff(second, \n" +
                "                           if(start_time < shift_start_time, shift_start_time, start_time),\n" +
                "                           if(end_time > shift_end_time, shift_end_time, end_time))\n" +
                "               else 0 end) stop_time_diff\n" +
                "   from (\n" +
                "       SELECT p.device_id, @date_id date_id, p.start_time, p.end_time, p.state, \n" +
                "              date_add(@date_id, interval ifnull(r.start_time, '00:00:00') hour_second) shift_start_time,\n" +
                "              date_add(@date_id, interval ifnull(r.end_time, '24:00:00') hour_second) shift_end_time\n" +
                "       FROM (select * from `"+database+"`.device_state_period, (select @date_id := '"+day+"') d\n" +
                "               where device_id = '"+deviceIdArr[0]+"' \n" +
                "                 and date(start_time) = @date_id\n" +
                "           ) p\n" +
                "       cross join `"+database+"`.shifts r\n" +
                "       ) a\n" +
                "   where (a.start_time <= a.shift_start_time and a.end_time > a.shift_start_time)\n" +
                "       or (a.start_time >= a.shift_start_time and a.end_time <= a.shift_end_time)\n" +
                "       or (a.start_time < a.shift_end_time and a.end_time >= a.shift_end_time)\n" +
                "   group by device_id, date_id, shift_start_time, shift_end_time, \n" +
                "           timestampdiff(second, shift_start_time, shift_end_time) \n" +
                "   ) l\n" +
                "cross join (select * from `"+database+"`.metadata where meta_id = '1') m\n" +
                "left join (select * from `"+database+"`.device where device_id = '"+deviceIdArr[0]+"' ) d \n" +
                "on l.device_id = d.device_id\n";
        for (int i = 1;i<deviceIdArr.length;i++){
            String joinSql = "union all (select l.device_id, l.date_id, l.shift_start_time, l.shift_end_time, d.device_unique_id as deviceName,\n" +
                    "      case when meta_definition = 1 then work_time_diff / (shift_time_diff - stop_time_diff)\n" +
                    "           when meta_definition = 0 then work_time_diff / shift_time_diff\n" +
                    "      end availability,\n" +
                    "       1 performance,\n" +
                    "       1 quality,\n" +
                    "      case when meta_definition = 1 then work_time_diff / (shift_time_diff - stop_time_diff)\n" +
                    "           when meta_definition = 0 then work_time_diff / shift_time_diff\n" +
                    "      end oee\n" +
                    "from (\n" +
                    "   select device_id, date_id, shift_start_time, shift_end_time, \n" +
                    "           timestampdiff(second, shift_start_time, shift_end_time) shift_time_diff,\n" +
                    "           sum(case when state = 1 then \n" +
                    "               timestampdiff(second, \n" +
                    "                           if(start_time < shift_start_time, shift_start_time, start_time),\n" +
                    "                           if(end_time > shift_end_time, shift_end_time, end_time))\n" +
                    "               else 0 end) work_time_diff,\n" +
                    "           sum(case when state = -1 then \n" +
                    "               timestampdiff(second, \n" +
                    "                           if(start_time < shift_start_time, shift_start_time, start_time),\n" +
                    "                           if(end_time > shift_end_time, shift_end_time, end_time))\n" +
                    "               else 0 end) stop_time_diff\n" +
                    "   from (\n" +
                    "       SELECT p.device_id, @date_id date_id, p.start_time, p.end_time, p.state, \n" +
                    "              date_add(@date_id, interval ifnull(r.start_time, '00:00:00') hour_second) shift_start_time,\n" +
                    "              date_add(@date_id, interval ifnull(r.end_time, '24:00:00') hour_second) shift_end_time\n" +
                    "       FROM (select * from `"+database+"`.device_state_period, (select @date_id := '"+day+"') d\n" +
                    "               where device_id = '"+deviceIdArr[i]+"' \n" +
                    "                 and date(start_time) = @date_id\n" +
                    "           ) p\n" +
                    "       cross join `"+database+"`.shifts r\n" +
                    "       ) a\n" +
                    "   where (a.start_time <= a.shift_start_time and a.end_time > a.shift_start_time)\n" +
                    "       or (a.start_time >= a.shift_start_time and a.end_time <= a.shift_end_time)\n" +
                    "       or (a.start_time < a.shift_end_time and a.end_time >= a.shift_end_time)\n" +
                    "   group by device_id, date_id, shift_start_time, shift_end_time, \n" +
                    "           timestampdiff(second, shift_start_time, shift_end_time) \n" +
                    "   ) l\n" +
                    "cross join (select * from `"+database+"`.metadata where meta_id = '1') m\n" +
                    "left join (select * from `"+database+"`.device where device_id = '"+deviceIdArr[i]+"' ) d \n" +
                    "on l.device_id = d.device_id)\n";
            findServiceByShiftNewSql += joinSql;
        }

        findServiceByShiftNewSql += "order by device_id,shift_start_time";
        return jdbcTemplate.queryForList(findServiceByShiftNewSql);
    }

    //查询某些设备名字
    public List<Map<String, Object>> findDeviceNameById(String database,String[] deviceIdArr){
        String sql = "select a.device_id,a.device_unique_id as deviceName from `"+database+"`.device a where a.device_id='"+deviceIdArr[0]+"'";
        for (int i=1;i<deviceIdArr.length;i++){
            sql += "or a.device_id='"+ deviceIdArr[i] +"'";
        }
        return  jdbcTemplate.queryForList(sql);
    }

    public List<Map<String,Object>> findDevicesByModelAndType(String database,String type,String model){
        return jdbcTemplate.queryForList(findDevicesByModelAndType.replace(databaseTag,database),type,model);
    }

    public List<Map<String,Object>> findIdleReasonByDevice(String database,String deviceId){
        return jdbcTemplate.queryForList(findIdleReasonByDevice.replace(databaseTag,database),deviceId);
    }

    //获取：最近的系统性能参数
    public List<Map<String,Object>> findRecentlyIndexParam(String database,String deviceId,String day){
        return jdbcTemplate.queryForList(findRecentlyIndexParam.replace(databaseTag,database),deviceId,day);
    }

    //获取：机床某天工作状态
    public List<Map<String,Object>> findWorkStatus(String database,String day,String deviceId){
        return jdbcTemplate.queryForList(findWorkStatus.replace(databaseTag,database),day,deviceId);
    }

    //获取数据：某天idle原因的分布
    public List<Map<String,Object>> findDayIdleReason(String database,String day,String deviceId){
        return jdbcTemplate.queryForList(findDayIdleReason.replace(databaseTag,database),deviceId,day,deviceId,day);
    }

    //oee 对比 一段时间(oee=availability)
    public List<Map<String,Object>> findDataForServiceCompareNew(String tenantId,String[] deviceIdArr,String startTime,String endTime){
        String findServiceCompareNewSql = "select n.device_id, n.date_id,c.device_unique_id as deviceName, \n" +
                "      ifnull(case when meta_definition = 1 then work_time_diff / (86400 - stop_time_diff)\n" +
                "                   when meta_definition = 0 then work_time_diff / 86400\n" +
                "              end, 0) availability,\n" +
                "       1 performance,\n" +
                "       1 quality,\n" +
                "      ifnull(case when meta_definition = 1 then work_time_diff / (86400 - stop_time_diff)\n" +
                "                   when meta_definition = 0 then work_time_diff / 86400\n" +
                "              end, 0) oee       \n" +
                "from ( SELECT device_id, date(start_time) date_id, m.meta_definition,\n" +
                "             sum(case when state = 1 then timestampdiff(second, start_time, end_time)\n" +
                "                   else 0 end ) work_time_diff,\n" +
                "             sum(case when state = -1 then timestampdiff(second, start_time, end_time)\n" +
                "                   else 0 end ) stop_time_diff\n" +
                "       FROM `"+tenantId+"`.device_state_period d\n" +
                "       cross join (select * from `"+tenantId+"`.metadata where meta_id = '1') m\n" +
                "       where state in (1, -1)\n" +
                "        and (device_id = '"+deviceIdArr[0]+"'\n";
        for (int i= 1;i<deviceIdArr.length;i++){
            String joinDeviceSql = "or device_id = '"+deviceIdArr[i]+"'\n";
            findServiceCompareNewSql += joinDeviceSql;
        }

        String joinSql = ")\n" +
                "        and date(start_time) between date('"+startTime+"') and date('"+endTime+"')\n" +
                "       group by device_id,date(start_time), m.meta_definition\n" +
                "    ) a\n" +
                "right join\n" +
                "   (select date_id,'"+deviceIdArr[0]+"' as device_id from `"+tenantId+"`.v_num where date_id between date('"+startTime+"') and date('"+endTime+"')\n";
        findServiceCompareNewSql += joinSql;

        for (int i = 1;i<deviceIdArr.length;i++){
            String joinDateNumSql = "union all \n" +
                    "        select date_id,'"+deviceIdArr[i]+"' as device_id from `"+tenantId+"`.v_num where date_id between date('"+startTime+"') and date('"+endTime+"')\n";
            findServiceCompareNewSql += joinDateNumSql;
        }
        findServiceCompareNewSql += ") n\n" +
                "on a.device_id= n.device_id\n" +
                "and a.date_id = n.date_id \n" +
                "left join `"+tenantId+"`.device c on n.device_id= c.device_id \n" +
                "order by date_id;";

        return jdbcTemplate.queryForList(findServiceCompareNewSql);
    }

    /**
     * faultTime 时间段
     * */
    public List<Map<String,Object>> getTimePeriod(String database,String date,String deviceId){
        return jdbcTemplate.queryForList(getFaultTimePeriod.replace(databaseTag,database),date,deviceId);
    }


    public List<Map<String,Object>> getDashboredOtherByDay(String database,String date,String deviceId){
        return jdbcTemplate.queryForList(getDownTimeRepairTable.replace(databaseTag,database),date,deviceId);
    }

    public List<Map<String,Object>> getDashboredOtherMTTRAndMTBF(String database,String deviceId,String meta_id,String endTime,String startTime){
        return jdbcTemplate.queryForList(getMTTRAndMTBF.replace(databaseTag,database),deviceId,meta_id,endTime,startTime);
    }
    public List<Map<String,Object>> getAllData(String database,String deviceId,String idleReason,String endTime,String startTime){
        return jdbcTemplate.queryForList(getAllData.replace(databaseTag,database),startTime,endTime,deviceId,idleReason,endTime,startTime);
    }

    public List<Map<String,Object>> getWeekDownTimePieChartData(String database,String deviceId,String endTime,String startTime){
        return jdbcTemplate.queryForList(getWeekDownTimePieChartData.replace(databaseTag,database),deviceId,startTime,endTime);
    }

    public List<Map<String,Object>> getWeekDownTimeBarChartData(String database,String deviceId,String endTime,String startTime){
        return jdbcTemplate.queryForList(getWeekDownTimeBarChartData.replace(databaseTag,database),deviceId,startTime,endTime);
    }

    /**
     * 获取某班次 performance availability quality oee数据
     * @param database
     * @param deviceIdArr   对比数组
     * @param day   选择日期
     * @return  deviceId deviceName 班次起始、结束时间 performance availability quality oee数据值
     */
    public List<Map<String, Object>> findServiceByShift (String database,String[] deviceIdArr,String day){

        String getServiceByShiftSql = "SELECT \n" +
                "    b.device_id,\n" +
                "    c.device_id,\n" +
                "    c.device_unique_id AS deviceName,\n" +
                "    b.start_time,\n" +
                "    b.end_time,\n" +
                "    b.availability,\n" +
                "    b.performance,\n" +
                "    b.quality,\n" +
                "    ROUND((b.availability * b.performance * b.quality),\n" +
                "            4) AS oee\n" +
                "FROM\n" +
                "    (SELECT \n" +
                "        a.device_id,\n" +
                "            a.start_time,\n" +
                "            a.end_time,\n" +
                "            CASE\n" +
                "                WHEN (TIMESTAMPDIFF(SECOND, a.start_time, a.end_time) - a.stop_diff_time) = 0 THEN 0\n" +
                "                ELSE a.work_diff_time / (TIMESTAMPDIFF(SECOND, a.start_time, a.end_time) - a.stop_diff_time)\n" +
                "            END AS availability,\n" +
                "            CASE\n" +
                "                WHEN (work_diff_time * prod_rate) = 0 THEN 0\n" +
                "                ELSE a.product_num / (work_diff_time * prod_rate)\n" +
                "            END AS performance,\n" +
                "            CASE\n" +
                "                WHEN a.product_num = 0 THEN 0\n" +
                "                ELSE a.good_prod_num / a.product_num\n" +
                "            END AS quality\n" +
                "    FROM\n" +
                "        `"+database+"`.product_info a where ( a.device_id = '"+deviceIdArr[0]+"'\n";

        for(int i = 1;i<deviceIdArr.length;i++){
            String joinSql = "OR a.device_id='" + deviceIdArr[i] + "'";
            getServiceByShiftSql += joinSql;
        }

        String sqlAfter =") AND DATE(a.start_time) = '"+ day +"') b\n" +
                "        JOIN\n" +
                "    `"+database+"`.device c ON b.device_id = c.device_id" +
                "    order by b.start_time;";
        getServiceByShiftSql += sqlAfter;
        return jdbcTemplate.queryForList(getServiceByShiftSql);
    }

    //oee 对比 一段时间(原 可选)
    public List<Map<String,Object>> getDataforServiceCompare(String sql){
        return jdbcTemplate.queryForList(sql);
    }

    public List<Map<String,Object>> getOtherPeriodLine(String database,String day,int type,String deviceId){
        return jdbcTemplate.queryForList(otherPeriod.replace(databaseTag,database),day,type,deviceId);
    }

    public Map<String,Object> getOtherPeriodState(String database,String day){
        try {
            return jdbcTemplate.queryForMap(stateOfPeriod.replace(databaseTag,database),day,day);
        }catch (EmptyResultDataAccessException e){
            return null;
        }
    }
}
